package cn.edu.hbcit.text1;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

//数据库处理
public class StudentDb {
    public static List<Student> selUserData(String acountNum) {
        List<Student> list = new ArrayList<>();
        Connection conn = DataBaseConnect.getCoon();
        String sql = "SELECT * FROM student WHERE aid = ?;";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, acountNum);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Student stu = new Student();
                stu.setStuId(rs.getString("sid"));
                stu.setStuMame(rs.getString("sname"));
                stu.setStuAge(rs.getInt("sage"));
                stu.setStuSex(rs.getString("ssex"));
                stu.setStuNativePlace(rs.getString("snativeplace"));
                stu.setStuMajor(rs.getString("smajor"));
                stu.setStuClass(rs.getString("sclass"));
                stu.setStuNation(rs.getString("snation"));
                list.add(stu);
            }
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public static List<Student> selName(String acountNum, String sName) {
        List<Student> list = new ArrayList<>();
        Connection conn = DataBaseConnect.getCoon();
        String sql = "SELECT * FROM student WHERE sname LIKE ? AND aid = ?;";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, sName);
            ps.setString(2, acountNum);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Student stu = new Student();
                stu.setStuId(rs.getString("sid"));
                stu.setStuMame(rs.getString("sname"));
                stu.setStuAge(rs.getInt("sage"));
                stu.setStuSex(rs.getString("ssex"));
                stu.setStuNativePlace(rs.getString("snativeplace"));
                stu.setStuMajor(rs.getString("smajor"));
                stu.setStuClass(rs.getString("sclass"));
                stu.setStuNation(rs.getString("snation"));
                list.add(stu);
            }
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public static boolean addStu(String myId, String name, int age, String sex, String place, String major, String myClass, String nation, String acount) {
        boolean flag = false;
        Connection conn = DataBaseConnect.getCoon();
        String sql = "INSERT INTO student\n" +
                "    VALUE (?, ?, ?, ?, ?,?,?, ?, ?);";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, myId);
            ps.setString(2, name);
            ps.setInt(3, age);
            ps.setString(4, sex);
            ps.setString(5, place);
            ps.setString(6, major);
            ps.setString(7, myClass);
            ps.setString(8, nation);
            ps.setString(9, acount);
            long num = ps.executeLargeUpdate();
            System.out.println(num);
            if (num > 0) {
                System.out.println("数据插入成功");
                flag = true;
            } else {
                System.out.println("数据插入失败");
                flag = false;
                conn.close();
            }
        } catch (SQLException e) {
            System.out.println("数据库插入异常");
        }
        return flag;
    }

    public static boolean delStu(String myId, String name, String acount) {
        boolean flag = false;
        Connection conn = DataBaseConnect.getCoon();
        String sql = "DELETE FROM student WHERE sname=? AND sid=?  AND aid=?;\n";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, name);
            ps.setString(2, myId);
            ps.setString(3, acount);
            long num = ps.executeLargeUpdate();
            System.out.println(num);
            if (num > 0) {
                System.out.println("数据删除成功");
                flag = true;
            } else {
                System.out.println("数据删除失败");
                flag = false;
                conn.close();
            }
        } catch (SQLException e) {
            System.out.println("数据库插入异常");
        }
        return flag;
    }

    public static boolean delStu(String myId, String acount) {
        boolean flag = false;
        Connection conn = DataBaseConnect.getCoon();
        String sql = "DELETE FROM student WHERE sid=?  AND aid=?;\n";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, myId);
            ps.setString(2, acount);
            long num = ps.executeLargeUpdate();
            System.out.println(num);
            if (num > 0) {
                System.out.println("数据删除成功");
                flag = true;
            } else {
                System.out.println("数据删除失败");
                flag = false;
            }
            conn.close();
        } catch (SQLException e) {
            System.out.println("数据库插入异常");
        }
        return flag;
    }
}